/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package com.dream.repository;

import com.dream.domain.TaskWork;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
* @website
* @author syl
* @date 2020-07-06
**/
public interface TaskWorkRepository extends JpaRepository<TaskWork, Integer>, JpaSpecificationExecutor<TaskWork> {

    String sql = "select i.`name`,u.username,sum(t.work_time) AS workHour,date_format(t.start_time, '%Y-%m-%d') AS startDate "
            + " from sys_task_work t "
            + " LEFT JOIN sys_dept i ON i.dept_id = t.dept_id "
            + " left join sys_user u on u.user_id = t.task_user_id "
            + " where i.pid is not null and date_format(t.start_time, '%Y-%m-%d') between ?1 and ?2 "
            + " GROUP BY date_format( t.start_time, '%Y-%m-%d' ),u.nick_name,t.dept_id ";
    @Query(value =sql,nativeQuery = true)
    public List<Object[]> queryByDate(String startDate, String endDate);

    String sql1 = "select i.`name`,u.username,sum(t.work_time) AS workHour,date_format(t.start_time, '%Y-%m-%d') AS startDate "
            + " from sys_task_work t "
            + " LEFT JOIN sys_dept i ON i.dept_id = t.dept_id "
            + " left join sys_user u on u.user_id = t.task_user_id "
            + " where i.pid is not null and date_format(t.start_time, '%Y-%m-%d') between ?1 and ?2 and t.dept_id =?3"
            + " GROUP BY date_format( t.start_time, '%Y-%m-%d' ),u.nick_name,t.dept_id ";
    @Query(value =sql1,nativeQuery = true)
    public List<Object[]> queryByDate(String startDate, String endDate,Integer deptId);

    String sql2 =  "select i.`name`,u.username,sum(t.work_time) AS workHour,date_format(t.start_time, '%Y-%m-%d') AS startDate "
            + " from sys_task_work t "
            + " LEFT JOIN sys_dept i ON i.dept_id = t.dept_id "
            + " left join sys_user u on u.user_id = t.task_user_id "
            + " where i.pid is not null and date_format(t.start_time, '%Y-%m-%d') between ?1 and ?2 and t.task_user_id =?3 "
            + " GROUP BY date_format( t.start_time, '%Y-%m-%d' ),u.nick_name,t.dept_id ";
    @Query(value =sql2,nativeQuery = true)
    public List<Object[]> queryByUserId(String startDate, String endDate,Integer taskUserId);

    String sql3 = "select i.`name`,u.username,sum(t.work_time) AS workHour,date_format(t.start_time, '%Y-%m-%d') AS startDate "
            + " from sys_task_work t "
            + " LEFT JOIN sys_dept i ON i.dept_id = t.dept_id "
            + " left join sys_user u on u.user_id = t.task_user_id "
            + " where i.pid is not null and date_format(t.start_time, '%Y-%m-%d') between ?1 and ?2 and t.leader_id =?3 "
            + " GROUP BY date_format( t.start_time, '%Y-%m-%d' ),u.nick_name,t.dept_id ";
    @Query(value =sql3,nativeQuery = true)
    public List<Object[]> queryByLeader(String startDate, String endDate,Integer leaderId);

    String sql4 = "select i.`name`,u.username,sum(t.work_time) AS workHour,date_format(t.start_time, '%Y-%m-%d') AS startDate "
            + " from sys_task_work t "
            + " LEFT JOIN sys_dept i ON i.dept_id = t.dept_id "
            + " left join sys_user u on u.user_id = t.task_user_id "
            + " where i.pid is not null and date_format(t.start_time, '%Y-%m-%d') between ?1 and ?2 and i.name =?3 "
            + " GROUP BY date_format( t.start_time, '%Y-%m-%d' ),u.nick_name,t.dept_id ";
    @Query(value =sql4,nativeQuery = true)
    public List<Object[]> queryByDateAndBlurry(String startDate, String endDate,String blurry);

    String sql5 = "select i.`name`,u.username,sum(t.work_time) AS workHour,date_format(t.start_time, '%Y-%m-%d') AS startDate "
            + " from sys_task_work t "
            + " LEFT JOIN sys_dept i ON i.dept_id = t.dept_id "
            + " left join sys_user u on u.user_id = t.task_user_id "
            + " where i.pid is not null and date_format(t.start_time, '%Y-%m-%d') between ?1 and ?2 and t.dept_id =?3 and i.name like '%?4 %'"
            + " GROUP BY date_format( t.start_time, '%Y-%m-%d' ),u.nick_name,t.dept_id ";
    @Query(value =sql5,nativeQuery = true)
    public List<Object[]> queryByDateAndBlurry(String startDate, String endDate,Integer deptId,String blurry);

    String sql6 =  "select i.`name`,u.username,sum(t.work_time) AS workHour,date_format(t.start_time, '%Y-%m-%d') AS startDate "
            + " from sys_task_work t "
            + " LEFT JOIN sys_dept i ON i.dept_id = t.dept_id "
            + " left join sys_user u on u.user_id = t.task_user_id "
            + " where i.pid is not null and date_format(t.start_time, '%Y-%m-%d') between ?1 and ?2 and t.task_user_id =?3 and i.name like '%?4 %'"
            + " GROUP BY date_format( t.start_time, '%Y-%m-%d' ),u.nick_name,t.dept_id ";
    @Query(value =sql6,nativeQuery = true)
    public List<Object[]> queryByUserIdAndBlurry(String startDate, String endDate,Integer taskUserId,String blurry);

    String sql7 = "select i.`name`,u.username,sum(t.work_time) AS workHour,date_format(t.start_time, '%Y-%m-%d') AS startDate "
            + " from sys_task_work t "
            + " LEFT JOIN sys_dept i ON i.dept_id = t.dept_id "
            + " left join sys_user u on u.user_id = t.task_user_id "
            + " where i.pid is not null and date_format(t.start_time, '%Y-%m-%d') between ?1 and ?2 and t.leader_id =?3 and i.name like '%?4 %'"
            + " GROUP BY date_format( t.start_time, '%Y-%m-%d' ),u.nick_name,t.dept_id ";
    @Query(value =sql7,nativeQuery = true)
    public List<Object[]> queryByLeaderAndBlurry(String startDate, String endDate,Integer leaderId,String blurry);
}

